/* QAD_CoBy-ProductsCostReport.p - Report Proxy Template                                       */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy !!old.p!! for use by QAD Reporting Framework.        */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table ttBaseProcess no-undo
   field pt_part    like pt_mstr.pt_part
   field desc47     like pt_mstr.pt_desc1
   field batch_um   like bom_mstr.bom_batch_um
   field batch_size like bom_mstr.bom_batch
   field cst_tot    like sct_det.sct_cst_tot
   field batch_cost like wo_mstr.wo_mtl_tot
.

define temp-table ttByProduct no-undo
   field pt_part       like pt_mstr.pt_part
   field pt_desc1      like pt_mstr.pt_desc1
   field pt_um         like pt_mstr.pt_um
   field prod_pct      like ps_mstr.ps_prod_pct
   field ps_qty_type   like ps_mstr.ps_qty_type
   field jp_batch_qty  like pt_mstr.pt_batch
   field unit_cost     like wo_mstr.wo_mtl_tot
   field jp_batch_cost like wo_mstr.wo_mtl_tot
.

define temp-table ttCoProduct no-undo
   field pt_part           like pt_mstr.pt_part
   field pt_desc1          like pt_mstr.pt_desc1
   field pt_um             like pt_mstr.pt_um
   field prod_pct          like ps_mstr.ps_prod_pct
   field ps_qty_type       like ps_mstr.ps_qty_type
   field jp_batch_qty      like pt_mstr.pt_batch
   field cst_pct           like ps_mstr.ps_cst_pct
   field calc_unit_cost    like wo_mstr.wo_mtl_tot
   field jp_batch_cost     like wo_mstr.wo_mtl_tot
   field set_unit_cost     like wo_mstr.wo_mtl_tot
   field isFromAnotherBase as   logical
.

define temp-table ttReportOptions no-undo
   field pt_bom_code like pt_mstr.pt_bom_code
   field eff_date    as   date
   field newpage     like mfc_ctrl.mfc_logical
   field site        like si_mstr.si_site
   field csset       like sct_det.sct_sim
.

define dataset dsReportResults for ttBaseProcess, ttByProduct, ttCoProduct, ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}
{com/qad/shell/report/reports/}
define variable vhDS as handle    no-undo.

empty temp-table ttBaseProcess   no-error.
empty temp-table ttByProduct     no-error.
empty temp-table ttCoProduct     no-error.
empty temp-table ttReportOptions no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_CoBy-ProductsCostReport.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

   delete object phReportResults no-error.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle(
         "ttReportOptions", "si_site", "DefaultValue", global_site).
   
   for first si_mstr no-lock where si_mstr.si_domain = global_domain 
      and  si_site = global_site:
   end.
   
   if available si_mstr
   then do:
       run SetFieldMetaParameter in ipReportHandle(
         "ttReportOptions", "csset", "DefaultValue", si_cur_set).
   end.

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString      as character no-undo.
   define variable hMainQuery       as handle    no-undo.
   define query    MainQuery        for bom_mstr.
   define variable site             like si_mstr.si_site no-undo.
   define variable csset            like sct_det.sct_sim no-undo.
   define variable eff_date         as date no-undo.
   define variable bom              like pt_mstr.pt_part no-undo.
   define variable bom1             like pt_mstr.pt_part no-undo.
   define variable newpage          like mfc_ctrl.mfc_logical no-undo.
   define variable desc47           like pt_mstr.pt_desc1 no-undo.
   define variable joint_label      like lngd_det.lngd_translation no-undo.
   define variable unit_cost        like wo_mstr.wo_mtl_tot no-undo.
   define variable set_unit_cost    like wo_mstr.wo_mtl_tot no-undo.
   define variable batch_cost       like wo_mstr.wo_mtl_tot no-undo.
   define variable jp_batch_cost    like batch_cost no-undo.
   define variable net_batch_cost   like batch_cost no-undo.
   define variable tot_lit          as character no-undo.
   define variable by_tot_cost      like batch_cost no-undo.
   define variable co_tot_cost1     like batch_cost no-undo.
   define variable co_tot_cost2     like batch_cost no-undo.
   define variable batch_size       like bom_mstr.bom_batch no-undo.
   define variable batch_um         like bom_mstr.bom_batch_um no-undo.
   define variable qty_per          like bom_mstr.bom_batch no-undo.
   define variable jp_batch_qty     like pt_mstr.pt_batch no-undo.
   define variable cst_pct          like ps_mstr.ps_cst_pct no-undo.
   define variable prod_pct         like ps_mstr.ps_prod_pct no-undo.
   define variable heading_reqd     like mfc_ctrl.mfc_logical extent 3 no-undo.
   define variable bom_code         like pt_mstr.pt_bom_code no-undo.
   define variable blnk             like wo_mstr.wo_part no-undo
      initial "                  ".
   define variable len              as integer no-undo.
   define variable pass             as integer no-undo.
   define variable calc_unit_cost   like wo_mstr.wo_mtl_tot no-undo.
   define buffer   ptmstr           for pt_mstr.
   define buffer   pt_mstr1         for pt_mstr.
   define buffer   bommstr          for bom_mstr.
   
   assign
      hMainQuery           = query MainQuery:handle
      site                 
      csset
      eff_date
      .

   for first si_mstr  where si_mstr.si_domain = global_domain
      and si_site = site no-lock:
   end.
   
   if not available si_mstr 
   then do:
      /* Site does not exist */
      {{&US_BBI}pxmsg.i &MSGNUM=708 &ERRORLEVEL=3}
      delete object phReportResults no-error.
      return.
   end.

   if si_db <> global_db 
   then do:
      /* Site is not assigned to this domain */
      {{&US_BBI}pxmsg.i &MSGNUM=6251 &ERRORLEVEL=3}
      delete object phReportResults no-error.
      return.
   end.

   for first cs_mstr  where cs_mstr.cs_domain = global_domain 
      and cs_set = csset no-lock:
   end.
   
   if not available cs_mstr 
   then do:
      /* Cost set does not exist */
      {{&US_BBI}pxmsg.i &MSGNUM=5407 &ERRORLEVEL=3} 
      delete object phReportResults no-error.
      return.
   end.

   queryString = "for each bom_mstr no-lock where bom_domain = "
                  + quoter(global_domain).

   run AddAllConditions in reportHandle
      ("bom_mstr", input-output queryString).

   queryString = queryString + ":".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:

      /* GET BASE ITEM-SITE DETAIL & ITEM MASTER */
      for first ptp_det
         where ptp_det.ptp_domain = global_domain
         and   ptp_part           = bom_parent
         and   ptp_site           = site
      no-lock:
      end.

      if available ptp_det
         and ptp_joint_type <> "5"
      then do:
         hMainQuery:get-next().
         next.
      end.

      for first pt_mstr
         where pt_mstr.pt_domain = global_domain
         and   pt_part           = bom_parent
      no-lock:
      end.

      if not available pt_mstr
      then do:
         hMainQuery:get-next().
         next.
      end.

      if not available ptp_det
         and pt_joint_type <> "5"
      then do:
         hMainQuery:get-next().
         next.
      end.

      assign
         desc47       = pt_desc1 + " " + pt_desc2
         batch_size   = bom_batch.

      if batch_size = 0
      then
         batch_size = 1.

      batch_um = bom_batch_um.

      /* GET sct_det COST RECORD */

      for first sct_det
         where sct_det.sct_domain = global_domain
         and   sct_sim            = csset
         and   sct_part           = pt_part
         and   sct_site           = site
      no-lock:
      end.

      if not available sct_det
      then do:
         hMainQuery:get-next().
         next.
      end.

      batch_cost = sct_cst_tot * batch_size.

      create ttBaseProcess.
      assign
         ttBaseProcess.pt_part      = pt_mstr.pt_part
         ttBaseProcess.desc47       = desc47
         ttBaseProcess.bom_batch_um = bom_mstr.bom_batch_um
         ttBaseProcess.batch_size   = batch_size
         ttBaseProcess.sct_cst_tot  = sct_det.sct_cst_tot
         ttBaseProcess.batch_cost   = batch_cost
      .

      /* BY-PRODUCT */
      by_tot_cost = 0.
      for each ps_mstr
         where ps_mstr.ps_domain  = global_domain
         and   (ps_comp           = bom_parent
                and ps_ps_code    = "J"
                and (eff_date = ?
                     or (eff_date         <> ?
                         and (ps_start     = ?
                              or ps_start <= eff_date)
                         and (ps_end       = ?
                              or eff_date <= ps_end)))
                and ps_joint_type > "1"
                and ps_joint_type < "5")
      use-index ps_comp
      no-lock:

         for first pt_mstr 
            where pt_mstr.pt_domain = global_domain
            and   pt_mstr.pt_part           = ps_par
         no-lock:
         end.

         /* GET sct_det COST RECORD */
         for first sct_det
            where sct_det.sct_domain = global_domain
            and   sct_sim            = csset
            and   sct_part           = pt_mstr.pt_part
            and   sct_site           = site
         no-lock:
         end.


         if available sct_det
         then
            unit_cost = sct_cst_tot.
         else
            unit_cost = 0.

         prod_pct = ps_prod_pct.

         assign
            jp_batch_qty  = ps_cop_qty  * (if ps_qty_type = ""
                                           then
                                              batch_size
                                           else
                                              1)
            jp_batch_cost = unit_cost   * jp_batch_qty
            by_tot_cost   = by_tot_cost + jp_batch_cost.

         create ttByProduct.
         assign
            ttByProduct.pt_part        = pt_mstr.pt_part
            ttByProduct.pt_desc1       = pt_mstr.pt_desc1
            ttByProduct.pt_um          = pt_mstr.pt_um
            ttByProduct.prod_pct       = prod_pct
            ttByProduct.ps_qty_type    = ps_mstr.ps_qty_type
            ttByProduct.jp_batch_qty   = jp_batch_qty
            ttByProduct.unit_cost      = unit_cost
            ttByProduct.jp_batch_cost  = jp_batch_cost
         .

      end. /* FOR EACH ps_mstr */

      /* BY-PRODUCT TOTAL DISPLAY */
      if by_tot_cost <> 0
      then do:
         net_batch_cost = max(0,batch_cost - by_tot_cost).
      end. /* IF by_tot_cost <> 0 */
      else do:
         net_batch_cost = batch_cost.
      end.

      assign
         co_tot_cost1 = 0
         co_tot_cost2 = 0
         pass         = 1.

      do while pass < 3:
         /* CO-PRODUCTS LISTED TWICE - 1ST WITH THOSE WITH PLANNING    */
         /* BOM EQUAL TO BASE, THEN THOSE WITH PLANNING BOM NOT EQUAL. */
         for each ps_mstr
            where ps_mstr.ps_domain  = global_domain
            and   (ps_comp           = bom_parent
                   and (eff_date     = ?
                        or (eff_date         <> ?
                            and (ps_start     = ?
                                 or ps_start <= eff_date)
                            and (ps_end       = ?
                                 or eff_date <= ps_end)))
                   and ps_ps_code    = "J"
                   and ps_joint_type = "1")
         use-index ps_comp
         no-lock:

            for first pt_mstr
               where pt_mstr.pt_domain = global_domain
               and   pt_part           = ps_par
            no-lock:
            end.

            for first ptp_det
               where ptp_det.ptp_domain = global_domain
               and   ptp_part           = ps_par
               and   ptp_site           = site
            no-lock:
            end.

            if pass = 1
               and ((available ptp_det
                        and ptp_bom_code <> bom_parent)
                    or (not available ptp_det
                           and pt_bom_code <> bom_parent))
            then 
               next.

            if pass = 2
               and ((available ptp_det
                        and ptp_bom_code = bom_parent)
                    or (not available ptp_det
                           and pt_bom_code = bom_parent))
            then
               next.

            assign
               cst_pct  = ps_cst_pct
               prod_pct = ps_prod_pct.

            if ps_qty_type = ""
            then
               jp_batch_qty = ps_cop_qty * batch_size.
            else
               jp_batch_qty = ps_cop_qty.

            assign
               jp_batch_cost = net_batch_cost * (cst_pct / 100)
               unit_cost     = jp_batch_cost / jp_batch_qty.

            if pass = 1
            then do:
               assign
                  isFromAnotherBase = false
                  co_tot_cost1      = co_tot_cost1 + jp_batch_cost
               .
            end. /* IF pass = 1 */

            if pass = 2
            then do:
               assign
                  isFromAnotherBase = true
                  co_tot_cost2      = co_tot_cost2 + jp_batch_cost
               .
            end. /* IF pass = 2 */

            /* GET sct_det COST RECORD */

            for first sct_det
               where sct_det.sct_domain = global_domain
               and   sct_sim            = csset
               and   sct_part           = pt_part
               and   sct_site           = site
            no-lock:
            end.

            if available sct_det
            then do:
               set_unit_cost = sct_cst_tot.
            end.
            else do:
               set_unit_cost = 0.
            end.

            calc_unit_cost = unit_cost.

            create ttCoProduct.
            assign
               ttCoProduct.pt_part           = pt_mstr.pt_part
               ttCoProduct.pt_desc1          = pt_mstr.pt_desc1
               ttCoProduct.pt_um             = pt_mstr.pt_um
               ttCoProduct.prod_pct          = prod_pct
               ttCoProduct.ps_qty_type       = ps_mstr.ps_qty_type
               ttCoProduct.jp_batch_qty      = jp_batch_qty
               ttCoProduct.cst_pct           = cst_pct
               ttCoProduct.calc_unit_cost    = calc_unit_cost
               ttCoProduct.jp_batch_cost     = jp_batch_cost
               ttCoProduct.set_unit_cost     = set_unit_cost
               ttCoProduct.isFromAnotherBase = isFromAnotherBase
            .

            /* RIGHT JUSTIFY BOM CODE */
            if pass = 2
            then do:
               
               if available ptp_det
               then do:
                  bom_code = ptp_bom_code.
               end.
               else do:
                  bom_code = pt_bom_code.
               end.

               assign
                  len                  = length(bom_code)
                  bom_code             = substring(blnk,1,(18 - len)) +
                                         substring(bom_code,1,(len))
                  ttCoProduct.pt_part  = ttCoProduct.pt_part + "~n" + bom_code.
            end. /* IF pass = 2 */
         end. /* FOR EACH ps_mstr */

         /* CO-PRODUCT SUB-TOTAL DISPLAY */
         if (pass = 1
                and co_tot_cost1 <> 0)
            or (pass = 2
                   and co_tot_cost2 <> 0)
         then do:

            if pass = 1
            then do:
               jp_batch_cost = co_tot_cost1.
            end.
            else do:
               jp_batch_cost = co_tot_cost2.
            end.

         end. /* IF (pass = 1 */
         pass = pass + 1.
      end. /* do while pass */

      hMainQuery:get-next().
   end. /* REPEAT QUERY */
   
   global_site = site.

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.
   delete object phReportResults no-error.

END PROCEDURE.
